package com.mks.demo.utils;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @Description
 * @Author hailong <1105744725@qq.com>
 * @Version V1.0.0
 * @Since 1.0
 * @Date 2019/6/5 11:43
 */
public class ExcelUtils {

    /**
     * 获取Excel文件单元格的值
     *
     * @param cell 单元格对象
     * @return
     */
    public static String getCellValue(XSSFCell cell) {
        //格式化number，string字符
        DecimalFormat df1 = new DecimalFormat("0.000");
        //日期格式化
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //时间格式化
        SimpleDateFormat sdf2 = new SimpleDateFormat("HHmmss");
        //格式化数字
        DecimalFormat df2 = new DecimalFormat("0.000");

        if (cell != null) {
            //字符
            if (CellType.STRING.compareTo(cell.getCellType()) == 0) {
                XSSFRichTextString cellStr = cell.getRichStringCellValue();
                if (cellStr != null) {
                    return cellStr.getString();
                }
                //数值
            } else if (CellType.NUMERIC.compareTo(cell.getCellType()) == 0) {
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    return df1.format(cell.getNumericCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    return sdf.format(cell.getDateCellValue());
                    //时间格式
                } else if ("[$-F400]h:mm:ss\\ AM/PM".equals(cell.getCellStyle().getDataFormatString())) {
                    Date date = cell.getDateCellValue();
                    return sdf2.format(date);
                } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    return sdf.format(date);
                } else {
                    return df2.format(cell.getNumericCellValue());
                }
                //公式
            } else if (CellType.FORMULA.compareTo(cell.getCellType()) == 0) {
                return cell.getCellFormula();
            }
        }
        return null;
    }

}
